package com.tyf.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.tyf.entity.Emp;
import com.tyf.util.DBManager;
import com.tyf.util.QueryDataResult;

/**
 * 员工数据库操作实现
 * 
 * @author tyf
 *
 */
public class EmpDaoImpl {
	/**
	 * 增
	 * 
	 * @param emp
	 * @return
	 */
	public boolean insertEmp(Emp emp) {
		String sql = "insert into emp (name,sex,sal,did,url) values('" + emp.getName() + "','" + emp.getSex() + "','"
				+ emp.getSal() + "','"+emp.getDid()+"','"+emp.getUrl()+"')";
		System.out.println("执行的sql语句是" + sql);
		return DBManager.execUpdate(sql);
	}

	/**
	 * 删除
	 * 
	 * @param id
	 * @return
	 */
	public boolean delEmp(int id) {
		String sql = "delete from emp where id=" + id;
		System.out.println("执行的sql语句是" + sql);
		return DBManager.execUpdate(sql);
	}

	/**
	 * 更新
	 * 
	 * @param emp
	 * @return
	 */
	public boolean updateEmp(Emp emp) {
		String sql = "update emp set name='" + emp.getName() + "',sex='" + emp.getSex() + "',sal='" + emp.getSal()
				+ "'where id=" + emp.getId();
		System.out.println("执行的sql语句是" + sql);
		return DBManager.execUpdate(sql);
	}

	/**
	 * 全查
	 * 
	 * @return
	 */
	public ArrayList<Emp> getAllEmp(String name) {
//		String sql = "select * from emp ";
//		if(name !=null && !name.equals("")) {
//			sql = sql +" where name like '%"+name+"%'";
//		}
		String sql = "select id,name,sex,sal,did,deptname,url from emp left join dept on emp.did = dept.deptid where 1=1 ";
		if(name !=null && !name.equals("")) {
			sql = sql +" and name like '%"+name+"%'";
		}
		System.out.println("执行的sql语句是" + sql);
		QueryDataResult execQuery = DBManager.executeQuery(sql);
		ArrayList<Emp> list = new ArrayList<Emp>();
		if (execQuery != null) {
			ResultSet rs = execQuery.rs;
			try {
				while (rs.next()) {
					Emp e = new Emp(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getInt(5), sql);
					e.setDeptName(rs.getString(6));
				    e.setUrl(rs.getString(7));
					list.add(e);
				}
			} catch (SQLException e) {
				// TODO: handle exception
				e.printStackTrace();
			} finally {
				execQuery.close();
			}
		}
		System.out.println(list);
		return list;
	}

	/**
	 * 根据id编号单查
	 * 
	 * @param id
	 * @return
	 */
	public Emp getEmpById(int id) {
		String sql = "select id,name,sex,sal,did,deptname,url from emp left join dept on emp.did = dept.deptid  where id=" + id;
		System.out.println("执行的sql语句是" + sql);
		QueryDataResult execQuery = DBManager.executeQuery(sql);
		if (execQuery != null) {
			ResultSet rs = execQuery.rs;
			try {
				if (rs.next()) {
					Emp e = new Emp(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getInt(5),sql);
					e.setDeptName(rs.getString(6));
					e.setUrl(rs.getString(7));
					return e;
				}
			} catch (SQLException e) {
				// TODO: handle exception
				e.printStackTrace();
			} finally {
				execQuery.close();
			}
		}
		return null;
	}

}
